package com.gjm.tool.utils;

import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.ReUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import lombok.AllArgsConstructor;
import lombok.Data;

import java.nio.file.Path;
import java.util.*;
import java.util.stream.Collectors;

public class GjmTest {
    public static void main(String[] args) {
        //计费和订单，组织机构id差异问题分析  规则编码：1729
//        test01();
        //orgid 数据查询sql拼接
//        test02();
        //全量接口整理
//        test03();
        //未销账数据整理
//        test04();
        //销账明细sql
//        test05();
//        test06();

//        test07();
//        test08();
//        test09();
//        test10();
        test11();
    }

    //计费和订单，组织机构id差异问题分析  规则编码：1729
    public static void test01() {
        //读取主数据的组织id
        List<List<String>> mainList = ExcelUtil.readExcel("C:\\Users\\gjm\\Desktop\\1729-main.xlsx", 1, Arrays.asList(1));
        List<String> mainOrgIdList = mainList.stream().map(i -> i.get(0)).collect(Collectors.toList());

        //读取报错的的组织id
        List<List<String>> errorList = ExcelUtil.readExcel("C:\\Users\\gjm\\Desktop\\1729.xlsx", 0, Arrays.asList(0));
        List<String> errorOrgIdList = errorList.stream().map(i -> i.get(0)).collect(Collectors.toList());

        List<String> resultList = errorOrgIdList.stream().filter(i -> !mainOrgIdList.contains(i)).collect(Collectors.toList());

        for (String str : resultList) {
            System.out.println(str);
        }
    }

    //orgid 数据查询sql拼接
    public static void test02() {
        String baseSql = "select TO_CHAR(%s) as orgid,'%s' as tableName from %s.%s t where t.%s in ('03900104','03900108','03900121','03900123','03900126','03900131','03900132','03900133','03900181','03900182','03900183','03900184','03900185','03900203','03900204','03900207','03900208','03900209','03900223','03900224','110002') union all";
        //读取主数据的组织id
        List<List<String>> list = ExcelUtil.readExcel("C:\\Users\\gjm\\Desktop\\orgid.xlsx", 0, Arrays.asList(1, 2, 3));
        for (List<String> strList : list) {
            String sql = String.format(baseSql, strList.get(2), strList.get(1), strList.get(0), strList.get(1), strList.get(2));
            System.out.println(sql);
        }
    }

    //全量接口整理
    public static void test03() {
        //读取全量接口整理
        List<List<String>> list = ExcelUtil.readExcel("C:\\Users\\gjm\\Desktop\\计费账务-信创改造（两翼总计划）.xlsx", 0, Arrays.asList(1, 2, 3));
        for (int i = 1; i < list.size(); i++) {
            List<String> strList = list.get(i);
            String sql = String.format("3.%s-----接口地址（%s）-----接口地址（%s）-----接口模块（%s）", i, strList.get(0), strList.get(1), strList.get(2));
            System.out.println(sql);
            System.out.println(String.format("3.%s.1 接口概述\n *******", i));
            System.out.println(String.format("3.%s.2 接口名称\n *******", i));
            System.out.println(String.format("3.%s.3 接口地址\n *******", i));
            System.out.println(String.format("3.%s.4 请求方式\n *******", i));
            System.out.println(String.format("3.%s.5 请求参数\n *******", i));
            System.out.println(String.format("3.%s.6 请求示例\n *******", i));
            System.out.println(String.format("3.%s.7 响应参数\n *******", i));
            System.out.println(String.format("3.%s.8 响应示例\n *******", i));
            System.out.println("\n");
        }
    }

    //未销账数据整理
    public static void test04() {
        //报账记录
        List<List<String>> applyList = ExcelUtil.readExcel("C:\\Users\\gjm\\Desktop\\bill_receive_apply.xlsx", 0, Arrays.asList(5, 6, 25));

        Map<String, List<List<String>>> CONTRACT_NO_MAP = applyList.stream().collect(Collectors.groupingBy(i -> i.get(1)));
        Map<String, List<List<String>>> RECEIVENO_MAP = applyList.stream().collect(Collectors.groupingBy(i -> i.get(2)));
        Map<String, List<List<String>>> ACCOUNT_ID_MAP = applyList.stream().collect(Collectors.groupingBy(i -> i.get(0)));


        //回款记录
        List<List<String>> logList = ExcelUtil.readExcel("C:\\Users\\gjm\\Desktop\\bill_payment_log.xlsx", 0, Arrays.asList(0, 1, 2));
        List orList = new ArrayList();
        for (List<String> str : logList) {
            String CONTRACT_NO = str.get(0);
            String RECEIVENO = str.get(1);
            String ACCOUNT_ID = str.get(2);
            if (CONTRACT_NO_MAP.get(CONTRACT_NO) == null && RECEIVENO_MAP.get(RECEIVENO) == null) {
//                System.out.println("------------------------------------------------------------");
//                List<List<String>> list = ACCOUNT_ID_MAP.get(ACCOUNT_ID);
                System.out.println(JSON.toJSONString(str));
//                orList.add(str);
//                System.out.println(JSON.toJSONString(list));
//                System.out.println("------------------------------------------------------------");
            }
        }
//        System.out.println(orList.size());
    }

    public static void test06() {
        List<List<String>> list = ExcelUtil.readExcel("C:\\Users\\gjm\\Desktop\\计费账务（两翼）-三月份工单分析详情-正式版.xlsx", 0, Arrays.asList(0, 1, 2, 3, 4));
        List<List<String>> rList = list.subList(1, list.size());
        for (int i = 0; i < rList.size(); i++) {
            List<String> str = rList.get(i);
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("序号", "序号" + (i + 1));
            jsonObject.put("工单编号", str.get(0));
            jsonObject.put("问题描述", str.get(1));
//            jsonObject.put("解决办法", str.get(2));
//            jsonObject.put("工单类型", str.get(3));
//            jsonObject.put("系统优化建议", str.get(4));
            System.out.println(jsonObject.toJSONString());
        }


    }


    /**
     * 生成解冻脚本
     */
    public static void test07() {
        List<Account> list = Arrays.asList(
                new Account("223569625", true, true, Arrays.asList("202501", "202502", "202504"), "202501"),
                new Account("251309694", true, true, Arrays.asList("202501", "202502", "202504"), "202501"),
                new Account("251316785", true, true, Arrays.asList("202501", "202504"), "202501"),
                new Account("251324416", true, true, Arrays.asList("202501", "202502", "202504"), "202501"));

        String bakDate = "bak_" + DateUtil.format(new Date(), "yyyyMMdd") + "01";

        //打印备份语句
        System.out.println("-- 备份语句");
        String base_sql_1 = "create table SNC.bill_book_settle_%s as select * from BILLING.bill_book_settle a where a.ACCOUNT_ID IN(%s);";
        List<String> list1 = list.stream().filter(i -> i.bookSettle).map(Account::getAccountId).collect(Collectors.toList());
        if (list1.size() > 0) {
            System.out.println(String.format(base_sql_1, bakDate, "'" + String.join("','", list1) + "'"));
        }

        String base_sql_2 = "create table SNC.bill_ownd_settle_%s as select * from BILLING.BILL_OWND_SETTLE a where a.ACCOUNT_ID IN(%s);";
        List<String> list2 = list.stream().filter(i -> i.owndSettle).map(Account::getAccountId).collect(Collectors.toList());
        if (list2.size() > 0) {
            System.out.println(String.format(base_sql_2, bakDate, "'" + String.join("','", list2) + "'"));
        }

        String base_sql_3 = "create table SNC.acc_account_info_%s as select * from BILLING.acc_account_info a where a.ACCOUNT_ID in(%s);";
        List<String> list3 = list.stream().map(Account::getAccountId).collect(Collectors.toList());
        if (list3.size() > 0) {
            System.out.println(String.format(base_sql_3, bakDate, "'" + String.join("','", list3) + "'"));
        }

        //打印执行语句
        System.out.println("\n-- 执行语句");

        String base_sql_4 = "UPDATE BILLING.bill_book_settle SET SETTLE_FEE = 0, SETTLE_TAX_FEE = 0 WHERE ACCOUNT_ID in (%s);";
        if (list1.size() > 0) {
            System.out.println(String.format(base_sql_4, "'" + String.join("','", list1) + "'"));
        }

        String base_sql_5 = "UPDATE BILLING.bill_ownd_settle a SET a.SETTLE_AMOUNT = 0,a.SETTLE_TAX_AMOUNT = 0 WHERE a.ACCOUNT_ID = '%s' and a.CYCLE_MONTH in (%s);";
        if (list2.size() > 0) {
            list.stream().filter(i -> i.owndSettle).collect(Collectors.toList()).forEach(i -> {
                System.out.println(String.format(base_sql_5, i.getAccountId(), "'" + String.join("','", i.getSettleMonth()) + "'"));
            });
        }

        String base_sql_6 = "UPDATE BILLING.acc_account_info SET UNSETTLED_MONTH = '%s' WHERE ACCOUNT_ID = '%s';";
        if (list2.size() > 0) {
            list.stream().filter(i -> i.owndSettle).collect(Collectors.toList()).forEach(i -> {
                System.out.println(String.format(base_sql_6, i.getMonth(), i.getAccountId()));
            });
        }

        //打印回滚语句
        System.out.println("\n-- 回滚语句");

        String base_sql_7 = "UPDATE BILLING.bill_book_settle a SET a.SETTLE_FEE = (SELECT sb.SETTLE_FEE FROM SNC.bill_book_settle_%s sb WHERE sb.ACCOUNT_ID = a.ACCOUNT_ID AND ROWNUM = 1), a.SETTLE_TAX_FEE = (SELECT sb.SETTLE_TAX_FEE FROM SNC.bill_book_settle_%s sb WHERE sb.ACCOUNT_ID = a.ACCOUNT_ID AND ROWNUM = 1) WHERE EXISTS (SELECT 1 FROM SNC.bill_book_settle_%s sb2 WHERE sb2.ACCOUNT_ID = a.ACCOUNT_ID);";
        if (list1.size() > 0) {
            System.out.println(String.format(base_sql_7, bakDate, bakDate, bakDate));
        }
        String base_sql_8 = "UPDATE BILLING.bill_ownd_settle a SET a.SETTLE_AMOUNT = (SELECT sb.SETTLE_AMOUNT FROM SNC.BILL_OWND_SETTLE_%s sb WHERE sb.ACCOUNT_ID = a.ACCOUNT_ID and sb.CYCLE_MONTH = a.CYCLE_MONTH AND ROWNUM = 1), a.SETTLE_TAX_AMOUNT = (SELECT sb3.SETTLE_TAX_AMOUNT FROM SNC.BILL_OWND_SETTLE_%s sb3 WHERE sb3.ACCOUNT_ID = a.ACCOUNT_ID and sb3.CYCLE_MONTH = a.CYCLE_MONTH AND ROWNUM = 1) WHERE EXISTS (SELECT 1 FROM SNC.BILL_OWND_SETTLE_%s sb2 WHERE sb2.ACCOUNT_ID = a.ACCOUNT_ID and sb2.CYCLE_MONTH = a.CYCLE_MONTH);";
        if (list2.size() > 0) {
            System.out.println(String.format(base_sql_8, bakDate, bakDate, bakDate));
        }
        String base_sql_9 = "UPDATE BILLING.acc_account_info a SET a.UNSETTLED_MONTH = (SELECT sb.UNSETTLED_MONTH FROM SNC.acc_account_info_%s sb WHERE sb.ACCOUNT_ID = a.ACCOUNT_ID AND ROWNUM = 1) WHERE EXISTS (SELECT 1 FROM SNC.acc_account_info_%s sb2 WHERE sb2.ACCOUNT_ID = a.ACCOUNT_ID);";
        if (list3.size() > 0) {
            System.out.println(String.format(base_sql_9, bakDate, bakDate));
        }

    }

    /**
     * 智联应收补齐客户编码和客户名称
     */
    public static void test10() {
        List<List<String>> list = ExcelUtil.readExcel("C:\\Users\\gjm\\Desktop\\SQL编辑器导出-30246.xlsx", 0, Arrays.asList(10, 15));
        List<List<String>> rList = list.subList(1, list.size());
        String base_sql = "update billing.acc_output_result set CUST_NO=(select CUSTOMER_NO from billing.ucs_account a left join billing.ucs_customer u on a.customer_id=u.customer_id where a.ACCOUNT_ID=%s),CUST_NAME=(select CUSTOMER_NAME from billing.ucs_account a left join billing.ucs_customer u on a.customer_id=u.customer_id where a.ACCOUNT_ID=%s) where  account_month='%s' and ACCOUNT_ID=%s;";
        for (List<String> datalist : rList) {
            System.out.println(String.format(base_sql, datalist.get(1), datalist.get(1), datalist.get(0), datalist.get(1)));
        }
    }

    /**
     * 智联应收补齐客户编码和客户名称
     */
    public static void test11() {
        List<List<String>> rows = new ArrayList<>();
        List<Path> pathList = FileUtil.getAllFilePath("D:\\workspace\\计费账务\\CT_BILLING_SourceCode\\billingcenter-microservicecloud-service");
        pathList = pathList.stream().filter(i -> i.toString().endsWith(".xml")).collect(Collectors.toList());
        for (Path path : pathList) {
            if (path.toString().contains("pom.xml") || path.toString().contains("spring.xml")|| path.toString().contains("\\target\\")) {
                continue;
            }
            System.out.println("xml文件名："+path.getFileName());
            System.out.println("所在路径："+path.toString());
            String content = FileUtil.readInfoFromFileToStr(path);
            List<String> selectIdList = ReUtil.findAll("(?:select|update|insert|delete).*?id\\s*=\\s*\"(.*?)\"", content,1);
            for (String selectId : selectIdList) {
                List<String> row = new ArrayList<>();
                row.add("service");
                row.add(path.getFileName().toString());
                row.add(path.toString().replace("D:\\workspace\\计费账务\\CT_BILLING_SourceCode\\billingcenter-microservicecloud-service\\src\\main",""));
                row.add(selectId.replace("id=\"","").replace("\"",""));
                row.add("未适配");
                row.add(selectIdList.size()+"");
                row.add("0");
                rows.add(row);
            }

        }
        ExcelUtil.writeExcel("C:\\Users\\Administrator\\Desktop\\sevice.xlsx",rows,new ArrayList<>() );
    }

    public static void test12() {
    }

    @Data
    @AllArgsConstructor
    static class Account {
        //账户id
        private String accountId;
        //账本是否冻结
        private Boolean bookSettle;
        //欠费是否冻结
        private Boolean owndSettle;
        //销账账期
        private List<String> settleMonth;
        //优先销账账期
        private String month;
    }
}
